/*
Script to create stored procedures to support two formatted code tables (country & state)
--*/
if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_COUNTRYFORMATTEDTYPECODE_CREATE') begin
  drop procedure USP_COUNTRYFORMATTEDTYPECODE_CREATE;
end;
go

/*
Question before I forget:
Do we even need to supply the @ID parameter to CREATES?  why not just get rid of the SqlDoWhat.Create for the ID param?
Leaving it alone b/c i'm probably missing something.
--*/
create procedure USP_COUNTRYFORMATTEDTYPECODE_CREATE
(
  @id uniqueidentifier = null output
 ,@description nvarchar(100)
 ,@abbreviation nvarchar(10)
 ,@ASPNETDBUSERID uniqueidentifier 
)
as begin
  if (@ID is null) or (@ID = '00000000-0000-0000-0000-000000000000') begin
    set @ID = newid();
  end;
  declare @CHANGEAGENTID uniqueidentifier = dbo.UFN_CHANGEAGENT_GET(@ASPNETDBUSERID)	
  insert into COUNTRY
  select
    @ID
   ,@description
   ,@abbreviation
   ,GETDATE()
   ,GETDATE()
   ,@CHANGEAGENTID
   ,@CHANGEAGENTID;  
end;
go

if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_STATEFORMATTEDTYPECODE_CREATE') begin
  drop procedure USP_STATEFORMATTEDTYPECODE_CREATE;
end;
go

create procedure USP_STATEFORMATTEDTYPECODE_CREATE
(
  @id uniqueidentifier = null output
 ,@description nvarchar(100)
 ,@abbreviation nvarchar(10)
 ,@ASPNETDBUSERID uniqueidentifier 
)
as begin
  if (@ID is null) or (@ID = '00000000-0000-0000-0000-000000000000') begin
    set @ID = newid();
  end;
  declare @CHANGEAGENTID uniqueidentifier = dbo.UFN_CHANGEAGENT_GET(@ASPNETDBUSERID)	
  
  insert into STATE
  select
    @id
   ,@description
   ,@abbreviation
   ,GETDATE()
   ,GETDATE()
   ,@CHANGEAGENTID
   ,@CHANGEAGENTID;  
end;
go

if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_COUNTRYFORMATTEDTYPECODE_GETBYID') begin
  drop procedure USP_COUNTRYFORMATTEDTYPECODE_GETBYID;
end;
go

create procedure USP_COUNTRYFORMATTEDTYPECODE_GETBYID
(
  @id uniqueidentifier
)
as begin
  select
    ID
   ,COUNTRYNAME AS DESCRIPTION
   ,COUNTRYABBREVIATION AS ABBREVIATION
   ,DATEADDED
   ,DATECHANGED
   ,ADDEDBYID
   ,CHANGEDBYID
  from
    COUNTRY
  where ID = @id;
end;
go

if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_STATEFORMATTEDTYPECODE_GETBYID') begin
  drop procedure USP_STATEFORMATTEDTYPECODE_GETBYID;
end;
go

create procedure USP_STATEFORMATTEDTYPECODE_GETBYID
(
  @id uniqueidentifier
)
as begin
  select
    ID
   ,STATENAME AS DESCRIPTION
   ,STATEABBREVIATION AS ABBREVIATION
   ,DATEADDED
   ,DATECHANGED
   ,ADDEDBYID
   ,CHANGEDBYID
  from
    STATE
  where ID = @id;
end;
go

if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_STATEFORMATTEDTYPECODE_DELETE') begin
  drop procedure USP_STATEFORMATTEDTYPECODE_DELETE;
end;
go

create procedure USP_STATEFORMATTEDTYPECODE_DELETE
(
  @id uniqueidentifier
)
as begin
  delete STATE where ID = @id;
end;
go

if exists(select * from INFORMATION_SCHEMA.ROUTINES where routine_name = 'USP_COUNTRYFORMATTEDTYPECODE_DELETE') begin
  drop procedure USP_COUNTRYFORMATTEDTYPECODE_DELETE;
end;
go

create procedure USP_COUNTRYFORMATTEDTYPECODE_DELETE
(
  @id uniqueidentifier
)
as begin
  delete COUNTRY where ID = @id;
end;
go